'create / retrieve / update / delete，即增删改查'
import pymysql

# 建立到数据库的连接
conn = pymysql.connect(
    host='127.0.0.1', port=3306,
    user='root', password='tedu.cn',
    db='nsd1910', charset='utf8'
)

# 创建游标，游标相当于是文件对象，通过文件对象可以对文件进行读写，通过游标可以对数据库进行增删改查
cur = conn.cursor()

# 编写并执行sql语句
# insert_dep = 'INSERT INTO departments VALUES (%s, %s)'
# cur.execute(insert_dep, (1, '人事部'))  # 插入一行记录
# cur.executemany(                       # 插入多行记录
#     insert_dep,
#     [(2, '运维部'), (3, '开发部'), (4, '测试部'), (5, '市场部'), (6, '财务部')]
# )

# 查询
# select1 = 'SELECT * FROM departments'
# cur.execute(select1)
# result1 = cur.fetchone()  # 取出第一条记录
# print(result1)
# result2 = cur.fetchmany(2)  # 继续取出2条记录
# print(result2)
# result3 = cur.fetchall()  # 取出剩余所有记录
# print(result3)

# 修改
# update1 = 'UPDATE departments SET dep_name=%s WHERE dep_name=%s'
# cur.execute(update1, ('人力资源部', '人事部'))

# 删除
del1 = 'DELETE FROM departments WHERE dep_id=%s'
cur.execute(del1, (6,))

# 确认
conn.commit()

# 关闭
cur.close()
conn.close()